﻿using System.Collections.Generic;
using System.Linq;
using System;
using KlauserswagModel;

namespace WebShopDAL
{
    public class ArticleDB
    {
        
        /// <summary>
        /// Creates a new article in the database.
        /// </summary>
        /// <param name="article">The new article to add.</param>
        /// <returns>int value of rows affected.</returns>
        public static int CreateArticle(Article article)
        {      
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Articles.Add(article);
                return context.SaveChanges();

            //    var sqlQuery = new StringBuilder()
            //        .Append("INSERT INTO [dbo].[Articles] ([Text] ,[UnitPrice],[PictureUrl],[ArticleName],[CategoryID],[InStock],[TotalOrdered])")
            //       .Append("VALUES (@Text , @UnitPrice, @PictureUrl, @ArticleName, @CategoryID, @InStock, @TotalOrdered)");

            //    using (var connection = ConnectionDB.GetConnection())
            //    {
            //        if (connection == null)
            //            throw new Exception("Connection is null in ArticleDB, CreateArticle");

            //        using (var command = new SqlCommand(sqlQuery.ToString(), connection))
            //        {
            //            command.Parameters.Add("Text", SqlDbType.Text).Value = article.Text;
            //            command.Parameters.Add("UnitPrice", SqlDbType.Float).Value = article.UnitPrice;
            //            command.Parameters.Add("PictureUrl", SqlDbType.Text).Value = article.PictureUrl;
            //            command.Parameters.Add("ArticleName", SqlDbType.NChar, 30).Value = article.ArticleName;
            //            command.Parameters.Add("CategoryID", SqlDbType.Int).Value = article.CategoryID;
            //            command.Parameters.Add("InStock", SqlDbType.Int).Value = article.InStock;
            //            command.Parameters.Add("TotalOrdered", SqlDbType.Int).Value = article.TotalOrdered;
            //            return command.ExecuteNonQuery();
            //        }
            //    }
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in ArticleDB, CreateArticle", ex);
            }
        }

        /// <summary>
        /// Gets all articles by categoryID.
        /// </summary>
        /// <param name="categoryID">The categoryID och the category.</param>
        /// <returns>List of Articles.</returns>
        public static List<Article> GetAllArticlesByCategory(int categoryID)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Articles.Where(a => a.CategoryID == categoryID).ToList();
                return query;

                //var sqlQuery = new StringBuilder()
                //.Append("SELECT [ArticleID],[Text],[UnitPrice],[PictureUrl],[ArticleName],[CategoryID],[InStock],[TotalOrdered]")
                //.Append("FROM [dbo].[Articles]")
                //.Append("WHERE [CategoryID] = @CategoryID");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in ArticleDB, GetAllArticlesByCategory");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("CategoryID", SqlDbType.Int).Value = categoryID;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        List<Article> articles = new List<Article>();
                //        if (dataSet.Tables[0].Rows.Count > 0)
                //        {
                //            for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                //            {
                //                articles.Add(new Article(
                //                    articleID: int.Parse(dataSet.Tables[0].Rows[i]["ArticleID"].ToString().Trim()),
                //                    text: dataSet.Tables[0].Rows[i]["Text"].ToString().Trim(),
                //                    unitPrice: float.Parse(dataSet.Tables[0].Rows[i]["UnitPrice"].ToString().Trim()),
                //                    pictureUrl: dataSet.Tables[0].Rows[i]["PictureUrl"].ToString().Trim(),
                //                    articleName: dataSet.Tables[0].Rows[i]["ArticleName"].ToString().Trim(),
                //                    categoryID: int.Parse(dataSet.Tables[0].Rows[i]["CategoryID"].ToString().Trim()),
                //                    inStock: int.Parse(dataSet.Tables[0].Rows[i]["InStock"].ToString().Trim()),
                //                    totalOrdered: int.Parse(dataSet.Tables[0].Rows[i]["TotalOrdered"].ToString().Trim())
                //                    ));
                //            }
                //            return articles;
                //        }
                //        return articles;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in ArticleDB, GetAllArticlesByCategory", ex);
            }
        }

        /// <summary>
        /// Gets an article by its articleID.
        /// </summary>
        /// <param name="articleID">The articleID for the article.</param>
        /// <returns>The article.</returns>
        public static Article GetArticleByArticleID(int articleID)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Articles.Where(a => a.ArticleID == articleID).ToList();

                var art = new Article();

                foreach (var article in query)
                {
                    art = article;
                }
                return art;

                //var sqlQuery = new StringBuilder()
                //.Append("SELECT [ArticleID],[Text],[UnitPrice],[PictureUrl],[ArticleName],[CategoryID],[InStock],[TotalOrdered]")
                //.Append("FROM [dbo].[Articles]")
                //.Append("WHERE [ArticleID] = @ArticleID");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in ArticleDB, GetArticleByArticleID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("ArticleID", SqlDbType.Int).Value = articleID;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);


                //        if (dataSet.Tables[0].Rows.Count > 0)
                //        {

                //            return new Article(
                //                      articleID: int.Parse(dataSet.Tables[0].Rows[0]["ArticleID"].ToString().Trim()),
                //                      text: dataSet.Tables[0].Rows[0]["Text"].ToString().Trim(),
                //                      unitPrice: float.Parse(dataSet.Tables[0].Rows[0]["UnitPrice"].ToString().Trim()),
                //                      pictureUrl: dataSet.Tables[0].Rows[0]["PictureUrl"].ToString().Trim(),
                //                      articleName: dataSet.Tables[0].Rows[0]["ArticleName"].ToString().Trim(),
                //                      categoryID: int.Parse(dataSet.Tables[0].Rows[0]["CategoryID"].ToString().Trim()),
                //                      inStock: int.Parse(dataSet.Tables[0].Rows[0]["InStock"].ToString().Trim()),
                //                      totalOrdered: int.Parse(dataSet.Tables[0].Rows[0]["TotalOrdered"].ToString().Trim())
                //                      );


                //        }
                //        return new Article();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in ArticleDB, GetArticleByArticleID", ex);
            }
        }

        /// <summary>
        /// Gets all articles in the database.
        /// </summary>
        /// <returns>A list with all articles.</returns>
        public static List<Article> GetAllArticles()
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Articles.ToList();
                return query;

                //var sqlQuery = new StringBuilder()
                //.Append("SELECT [ArticleID],[Text],[UnitPrice],[PictureUrl],[ArticleName],[CategoryID],[InStock],[TotalOrdered]")
                //.Append("FROM [dbo].[Articles]");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in ArticleDB, GetAllArticles");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        List<Article> articles = new List<Article>();
                //        if (dataSet.Tables[0].Rows.Count > 0)
                //        {
                //            for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                //            {
                //                articles.Add(new Article(
                //                    articleID: int.Parse(dataSet.Tables[0].Rows[i]["ArticleID"].ToString().Trim()),
                //                    text: dataSet.Tables[0].Rows[i]["Text"].ToString().Trim(),
                //                    unitPrice: float.Parse(dataSet.Tables[0].Rows[i]["UnitPrice"].ToString().Trim()),
                //                    pictureUrl: dataSet.Tables[0].Rows[i]["PictureUrl"].ToString().Trim(),
                //                    articleName: dataSet.Tables[0].Rows[i]["ArticleName"].ToString(),
                //                    categoryID: int.Parse(dataSet.Tables[0].Rows[i]["CategoryID"].ToString().Trim()),
                //                    inStock: int.Parse(dataSet.Tables[0].Rows[i]["InStock"].ToString().Trim()),
                //                    totalOrdered: int.Parse(dataSet.Tables[0].Rows[i]["TotalOrdered"].ToString().Trim())
                //                    ));
                //            }
                //            return articles;
                //        }
                //        return articles;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in ArticleDB, GetArticleByArticleID", ex);
            }
        }

        /// <summary>
        /// Updates an article in the database.
        /// </summary>
        /// <param name="article">The article to update.</param>
        /// <returns>Int value of rows affected.</returns>
        public static int UpdateArticle(Article article)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Articles.Where(a => a.ArticleID == article.ArticleID);
                return context.SaveChanges();

                //var sqlQuery = new StringBuilder()
                //.Append("UPDATE [dbo].[Articles]")
                //.Append("SET [Text] = @Text,[UnitPrice] = @UnitPrice,[PictureUrl] = @PictureUrl,[ArticleName] = @ArticleName,[CategoryID] = @CategoryID,[InStock] = @InStock,[TotalOrdered] = @TotalOrdered ")
                //.Append("WHERE [ArticleID] = @ArticleID");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in ArticleDB, UpdateArticle");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("ArticleID", SqlDbType.Int).Value = article.ArticleID;
                //        command.Parameters.Add("Text", SqlDbType.Text).Value = article.Text;
                //        command.Parameters.Add("UnitPrice", SqlDbType.Float).Value = article.UnitPrice;
                //        command.Parameters.Add("PictureUrl", SqlDbType.Text).Value = article.PictureUrl;
                //        command.Parameters.Add("ArticleName", SqlDbType.NChar, 30).Value = article.ArticleName;
                //        command.Parameters.Add("CategoryID", SqlDbType.Int).Value = article.CategoryID;
                //        command.Parameters.Add("InStock", SqlDbType.Int).Value = article.InStock;
                //        command.Parameters.Add("TotalOrdered", SqlDbType.Int).Value = article.TotalOrdered;
                //        return command.ExecuteNonQuery();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in ArticleDB, UpdateArticle", ex);
            }
        }

        /// <summary>
        /// Deletes an article from the database.
        /// </summary>
        /// <param name="articleID">The articeId of the article.</param>
        /// <returns>Int value of affected rows.</returns>
        public static int DeleteArticle(int articleID)
        {
            try
            {
                var context = new KlauserswagEntities();
                var query = context.Articles.Where(a => a.ArticleID == articleID);
                return context.SaveChanges();

                //var sqlQuery = new StringBuilder()
                //    .Append("DELETE FROM [dbo].[Articles] ")
                //    .Append("WHERE [ArticleID] = @ArticleID ");

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in ArticleDB, DeleteArticle");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("ArticleID", SqlDbType.Int).Value = articleID;
                //        return command.ExecuteNonQuery();
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in ArticleDB, DeleteArticle", ex);
            }
        }
    }
}
